/**
 * Copyright &copy; 2012-2016 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved.
 */
package com.thinkgem.jeesite.common.utils.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;

import com.thinkgem.jeesite.common.persistence.DataEntity;
import com.thinkgem.jeesite.modules.sys.entity.User;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import com.google.common.collect.Lists;
import com.thinkgem.jeesite.common.utils.DateUtils;
import com.thinkgem.jeesite.common.utils.Reflections;
import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField;
import com.thinkgem.jeesite.common.utils.excel.fieldtype.RoleListType;
import com.thinkgem.jeesite.modules.sys.utils.DictUtils;

/**
 * 导入Excel文件（支持“XLS”和“XLSX”格式）
 * @author ThinkGem
 * @version 2013-03-10
 */
public class ImportExcel {
	
	private static Logger log = LoggerFactory.getLogger(ImportExcel.class);
			
	/**
	 * 工作薄对象
	 */
	private Workbook wb;
	
	/**
	 * 工作表对象
	 */
	private Sheet sheet;
	
	/**
	 * 标题行号
	 */
	private int headerNum;
	

	public ImportExcel(String fileName, int headerNum) 
			throws InvalidFormatException, IOException {
		this(new File(fileName), headerNum);
	}
	

	public ImportExcel(File file, int headerNum) 
			throws InvalidFormatException, IOException {
		this(file, headerNum, 0);
	}

	/**
	 * 构造函数
	 * @param fileName 导入文件
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @param sheetIndex 工作表编号
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(String fileName, int headerNum, int sheetIndex) 
			throws InvalidFormatException, IOException {
		this(new File(fileName), headerNum, sheetIndex);
	}
	
	/**
	 * 构造函数
	 * @param file 导入文件对象
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @param sheetIndex 工作表编号
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(File file, int headerNum, int sheetIndex) 
			throws InvalidFormatException, IOException {
		this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);
	}
	
	/**
	 * 构造函数
	 * @param multipartFile 导入文件对象
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @param sheetIndex 工作表编号
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex) 
			throws InvalidFormatException, IOException {
		this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);
	}

	/**
	 * 构造函数
	 * @param fileName
	 * @param is 导入文件对象
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @param sheetIndex 工作表编号
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex) 
			throws InvalidFormatException, IOException {
		if (StringUtils.isBlank(fileName)){
			throw new RuntimeException("导入文档为空!");
		}else if(fileName.toLowerCase().endsWith("xls")){    
			this.wb = new HSSFWorkbook(is);    
        }else if(fileName.toLowerCase().endsWith("xlsx")){  
        	this.wb = new XSSFWorkbook(is);
        }else{  
        	throw new RuntimeException("文档格式不正确!");
        }  
		if (this.wb.getNumberOfSheets()<sheetIndex){
			throw new RuntimeException("文档中没有工作表!");
		}
		this.sheet = this.wb.getSheetAt(sheetIndex);
		this.headerNum = headerNum;
		log.debug("Initialize success.");
	}
	
	/**
	 * 获取行对象
	 * @param rownum
	 * @return
	 */
	public Row getRow(int rownum){
		return this.sheet.getRow(rownum);
	}

	/**
	 * 获取数据行号
	 * @return
	 */
	public int getDataRowNum(){
		return headerNum+1;
	}
	
	/**
	 * 获取最后一个数据行号
	 * @return
	 */
	public int getLastDataRowNum(){
		return this.sheet.getLastRowNum()+headerNum;
	}
	
	/**
	 * 获取最后一个列号
	 * @return
	 */
	public int getLastCellNum(){
		return this.getRow(headerNum).getLastCellNum();
	}
	
	/**
	 * 获取单元格值
	 * @param row 获取的行
	 * @param column 获取单元格列号
	 * @return 单元格值
	 */
	public Object getCellValue(Row row, int column){
		Object val = "";
		try{
			Cell cell = row.getCell(column);
			if (cell != null){
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
					val = cell.getNumericCellValue();
					if(String.valueOf(val).indexOf("E")>0){
						DecimalFormat df = new DecimalFormat("0");
						val = df.format(val);
					}
				}else if (cell.getCellType() == Cell.CELL_TYPE_STRING){
					val = cell.getStringCellValue();
				}else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){
					val = cell.getCellFormula();
				}else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
					val = cell.getBooleanCellValue();
				}else if (cell.getCellType() == Cell.CELL_TYPE_ERROR){
					val = cell.getErrorCellValue();
				}
			}
		}catch (Exception e) {
			return val;
		}
		return val;
	}
	
	/**
	 * 获取导入数据列表
	 * @param cls 导入对象类型
	 * @param groups 导入分组
	 */
	public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException{
		List<Object[]> annotationList = Lists.newArrayList();
		List<ExcelField> userFieldSort=null;
		// Get annotation field 
		Field[] fs = cls.getDeclaredFields();
		for (Field f : fs){
			ExcelField ef = f.getAnnotation(ExcelField.class);
			ExportExcel.addAnnotationList(ef,groups,2,f,annotationList);
		}
		// Get annotation method
		Method[] ms = cls.getDeclaredMethods();
		for (Method m : ms){
			ExcelField ef = m.getAnnotation(ExcelField.class);
			List<ExcelField> list=ExportExcel.addAnnotationList(ef,groups,2,m,annotationList);
			if(list!=null &&list.size()>0){
				userFieldSort=list;
			}
		}
		// Field sorting
		Collections.sort(annotationList, new Comparator<Object[]>() {
			public int compare(Object[] o1, Object[] o2) {
				return new Integer(((ExcelField)o1[0]).sort()).compareTo(
						new Integer(((ExcelField)o2[0]).sort()));
			};
		});
		List<E> dataList = Lists.newArrayList();
		for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) {
			E e = (E)cls.newInstance();
			invokeBean(e,annotationList,i,userFieldSort);
			dataList.add(e);
		}
		return dataList;
	}

	/**
	 * 将单元格对象反射为Bean
	 * @param e 反射接受对象
	 * @param annotationList 所需反射的字段
	 * @param i 单元格第n行
	 * @param <E>
	 */
	public <E> void invokeBean(E e, List<Object[]> annotationList, int i,List<ExcelField> userFieldSort){
		Row row = this.getRow(i);
		StringBuilder sb = new StringBuilder();
		int column = 0;
		User user=new User();
		for (Object[] os : annotationList){
			Object val = this.getCellValue(row, column++);
			if (val != null){
				ExcelField ef = (ExcelField)os[0];
				Object c;
				if(userFieldSort!=null && userFieldSort.contains(ef)){//组装User对象
					c=getField(val,ef,Class.class,os,column,i);
					String mthodName = ((Method)os[1]).getName();
					if ("get".equals(mthodName.substring(0, 3))){
						mthodName = "set"+StringUtils.substringAfter(mthodName, "get");
					}
					Reflections.invokeMethod(user, mthodName, new Class[] {c.getClass()}, new Object[] {c});
					userFieldSort.remove(ef);
					if(userFieldSort.size()==0){
						Reflections.invokeMethod(e, "setUser", new Class[] {User.class}, new Object[] {user});
					}
					sb.append(val+", ");
					continue;
				}else{
					c=getField(val,ef,Class.class,os,column,i);
				}
				// set entity value
				if (os[1] instanceof Field){
					Reflections.invokeSetter(e, ((Field)os[1]).getName(), val);
				}else if (os[1] instanceof Method){
					String mthodName = ((Method)os[1]).getName();
					if ("get".equals(mthodName.substring(0, 3))){
						mthodName = "set"+StringUtils.substringAfter(mthodName, "get");
					}
					//判断方法是否是属于某对象属性（主要针对User属性对象）
					if(os.length>2 && User.class == ((Method)os[2]).getReturnType()){//是
						Method curM = (Method) os[2];
						System.out.println("当前导入列方法信息"+ curM.getName()+" "+curM.getReturnType());
						System.out.println(User.class == curM.getReturnType());
						try {
							Object curUser = curM.invoke(e);
							if(curUser == null)curUser = new User();
							Reflections.invokeMethod(curUser, mthodName, new Class[] {c.getClass()}, new Object[] {c});
							String setUserMethodName = "set"+StringUtils.substringAfter(curM.getName(), "get");
							Reflections.invokeMethod(e, setUserMethodName, new Class[] {curUser.getClass()}, new Object[] {curUser});
						} catch (Exception e1) {
							e1.printStackTrace();
						}
					}else{
						//如果单元格内容为null 使用get方法的返回值类型
						Reflections.invokeMethod(e, mthodName, new Class[] {c == null ? (((Method)os[1]).getReturnType()) :c.getClass()}, new Object[] {c});
					}
				}
			}
			sb.append(val+", ");
		}
		log.debug("Read success: ["+i+"] "+sb.toString());
	}

	/**
	 * 获取基类每个属性的值
	 * @param val
	 * @param ef
	 * @param valType
	 * @param os
	 * @param column
	 * @param i
	 */
	public Object getField(Object val,ExcelField ef,Class<?> valType,Object[] os,int column,int i){
		// If is dict type, get dict value
		if (StringUtils.isNotBlank(ef.dictType())){//字典数据
			val = DictUtils.getDictValue(val.toString(), ef.dictType(), "");
			//log.debug("Dictionary type value: ["+i+","+colunm+"] " + val);
		}
		// Get param type and type cast
		if (os[1] instanceof Field){//注解打在类上的情况
			valType = ((Field)os[1]).getType();
		}else if (os[1] instanceof Method){//注解打在方法上
			Method method = ((Method)os[1]);
			if ("get".equals(method.getName().substring(0, 3))){
				valType = method.getReturnType();
			}else if("set".equals(method.getName().substring(0, 3))){
				valType = ((Method)os[1]).getParameterTypes()[0];
			}
		}
		//log.debug("Import value type: ["+i+","+column+"] " + valType);
		try {
			if (valType == String.class){
				String s = String.valueOf(val.toString());
				if(StringUtils.endsWith(s, ".0")){
					val = StringUtils.substringBefore(s, ".0");
				}else{
					val = String.valueOf(val.toString());
				}
			}else if (valType == Integer.class){
				val = Integer.valueOf(val.toString()).intValue();
			}else if (valType == Long.class){
				val = Long.valueOf(val.toString()).longValue();
			}else if (valType == Double.class){
				val = Double.valueOf(val.toString());
			}else if (valType == Float.class){
				val = Float.valueOf(val.toString());
			}else if (valType == Date.class){
				//val = DateUtil.getJavaDate((Double)val);
				val = DateUtils.parseDate(val);
			}else{
				/*if (ef.fieldType() != Class.class){
					val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString());
				}else{}*/
				if (ef.fieldType() != Class.class && ef.fieldType() == RoleListType.class){
					val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString());
				}else{
					Object o = Class.forName(valType.getName()).newInstance();
					Reflections.invokeMethod(o, "setName", new Class[] { String.class}, new Object[] { String.valueOf(val.toString())});
					val=o;
				}
			}
		} catch (Exception ex) {
			log.info("Get cell value ["+i+","+column+"] error: " + ex.toString());
			val = null;
		}
		return val;
	}//	/**
//	 * 导入测试
//	 */
//	public static void main(String[] args) throws Throwable {
//		
//		ImportExcel ei = new ImportExcel("target/export.xlsx", 1);
//		
//		for (int i = ei.getDataRowNum(); i < ei.getLastDataRowNum(); i++) {
//			Row row = ei.getRow(i);
//			for (int j = 0; j < ei.getLastCellNum(); j++) {
//				Object val = ei.getCellValue(row, j);
//				System.out.print(val+", ");
//			}
//			System.out.print("\n");
//		}
//		
//	}

}
